﻿
/*
 =====================================================
 * Copyright © 2022 IceToad All rights reserved.
 * 作者: IceToad
 * 创建时间: 2022/11/09 10:09:09
 * 版本: v1.0.1
 * 版权说明: 引用或转载请注明源
 * ----------------------------------------------------
 * 文件名: SqlServerHelp.cs
 * 程序集: YYADONET
 * 文件功能: 提供对SQL Server数据库的增删改查等访问功能。
 * CSDN：https://blog.csdn.net/weixin_44312699
 * Gitee：https://gitee.com/icetoads
 * 语雀：https://www.yuque.com/icetoad
 * 公众号：【冰蟾DotNet】（后台可留言）
 * ----------------------------------------------------
 * 说明：
 * 本项目提供功能有限，难免有考虑不到的情况，如发现错误或有什么建议，欢迎指正
 ======================================================
*/

using System;
using System.Data.SqlClient;
using System.Text;
using System.Data;

namespace YYADONET
{
    public class SqlServerHelp
    {
        #region 通用参数
        /// <summary>
        /// SqlServer连接
        /// </summary>
        private SqlConnection conn;
        /// <summary>
        /// 事物
        /// </summary>
        SqlTransaction newTran;
        #endregion

        #region 构造函数
        /// <summary>
        /// 初始化数据库连接
        /// </summary>
        /// <param name="strConn">连接字符串</param>
        /// 
        public SqlServerHelp(string strConn)
        {
            conn = new SqlConnection(strConn);
        }

        #endregion

        #region 通用操作
        /// <summary>
        /// 打开数据库
        /// </summary>
        public void Open()
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            if (conn.State == ConnectionState.Broken)
            {
                conn.Close();
                conn.Open();
            }
        }
        /// <summary>
        /// 关闭数据库
        /// </summary>
        public void Close()
        {
            if (conn.State == ConnectionState.Closed)
            {
                return;
            }
            try
            {
                conn.Close();
            }
            catch
            {

            }
        }
        #endregion

        #region 更新数据库
        /// <summary>
        /// 更新数据库
        /// </summary>
        /// <param name="sql">执行语句</param>
        /// <returns>受影响的行数</returns>
        public int NonQuery(string sql)
        {
            int test = 0;
            try
            {
                SqlCommand cmd = new SqlCommand();
                Open();
                newTran = conn.BeginTransaction();
                cmd.Transaction = newTran;//为命令指定事务
                cmd.CommandText = sql;
                cmd.Connection = conn;
                test = cmd.ExecuteNonQuery();
                newTran.Commit();//提交数据库事物
                cmd.Dispose();
            }
            catch (Exception EX)
            {
                newTran.Rollback();//事务回滚
                throw new Exception(EX.Message + "执行出错！:" + sql);
            }
            finally
            {
                Close();
            }
            return test;
        }
        /// <summary>
        /// 有变量更新数据库
        /// </summary>
        /// <param name="sql">执行语句</param>
        /// <param name="para">SqlParameter参数</param>
        /// <returns>受影响的行数</returns>
        public int NonQuery(string sql, SqlParameter[] para)
        {

            int test = 0;
            try
            {
                SqlCommand cmd = new SqlCommand();
                Open();
                newTran = conn.BeginTransaction();
                cmd.Transaction = newTran;//为命令指定事务
                cmd.CommandText = sql;
                cmd.Connection = conn;
                cmd.Parameters.AddRange(para);
                test = cmd.ExecuteNonQuery();
                newTran.Commit();//提交数据库事物
                cmd.Dispose();
            }
            catch (Exception EX)
            {
                newTran.Rollback();//事务回滚
                StringBuilder sqlArr = new StringBuilder();
                foreach (SqlParameter par in para)
                {
                    sqlArr.Append(par.ParameterName + ":" + par.Value.ToString());
                }
                throw new Exception(EX.Message + "执行出错！:" + sql + ";" + sqlArr.ToString());
            }
            finally
            {
                Close();
            }
            return test;
        }
        #endregion

        #region 获取首行首列
        /// <summary>
        /// 获取首行首列
        /// </summary>
        /// <param name="sql">执行语句</param>
        /// <returns>object</returns>
        public object Scalar(string sql)
        {
            object test = "";
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                Open();
                test = cmd.ExecuteScalar();
                cmd.Dispose();
            }
            catch (Exception EX)
            {
                throw new Exception(EX.Message + "语句执行出错！:" + sql);
            }
            finally
            {
                Close();
            }
            return test;
        }
        /// <summary>
        /// 返回执行状态、获取首行首列
        /// </summary>
        /// <param name="sql">执行语句</param>
        /// <param name="exc">接收返回执行的内容</param>
        /// <returns>int执行结果：0成功、1失败</returns>
        public int Scalar(string sql, out object exc)
        {
            int sign = 0;
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                Open();
                exc = cmd.ExecuteScalar();
                cmd.Dispose();
                if (exc == null)
                {
                    exc = "没有查询到任何数据";
                    sign = 1;
                }
            }
            catch (Exception EX)
            {
                exc = EX.Message + "语句执行出错！:" + sql;
                sign = 1;
            }
            finally
            {
                Close();
            }
            return sign;
        }

        /// <summary>
        /// 有变量获取首行首列
        /// </summary>
        /// <param name="sql">执行语句</param>
        /// <param name="para">SqlParameter参数</param>
        /// <returns>object</returns>
        public object Scalar(string sql, SqlParameter[] para)
        {
            object test = 0;
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                Open();
                cmd.Parameters.AddRange(para);
                test = cmd.ExecuteScalar();
                cmd.Dispose();
            }
            catch (Exception EX)
            {
                StringBuilder sqlArr = new StringBuilder();
                foreach (SqlParameter par in para)
                {
                    sqlArr.Append(par.ParameterName + ":" + par.Value.ToString());
                }
                throw new Exception(EX.Message + "执行出错！:" + sql + ";" + sqlArr.ToString());
            }
            finally
            {
                Close();
            }
            return test;
        }
        /// <summary>
        /// 有变量返回执行状态、获取首行首列
        /// </summary>
        /// <param name="sql">执行语句</param>
        /// <param name="para">SqlParameter参数</param>
        /// <param name="exc">返回的查询内容</param>
        /// <returns>object</returns>
        public int Scalar(string sql, SqlParameter[] para, out object exc)
        {
            int sign = 0;
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                Open();
                cmd.Parameters.AddRange(para);
                exc = cmd.ExecuteScalar();
                cmd.Dispose();
                if (exc == null)
                {
                    exc = "没有查询到任何数据";
                    sign = 1;
                }
            }
            catch (Exception EX)
            {
                StringBuilder sqlArr = new StringBuilder();
                foreach (SqlParameter par in para)
                {
                    sqlArr.Append(par.ParameterName + ":" + par.Value.ToString());
                }
                exc = EX.Message + "执行出错！:" + sql + ";" + sqlArr.ToString();
                sign = 1;
            }
            finally
            {
                Close();
            }
            return sign;
        }
        #endregion

        #region 查询DataReader
        /// <summary>
        /// 无变量查询数据库返回DataReader
        /// 获取到DataReader后必须手动调用关闭方法Close():
        /// </summary>
        /// <param name="cmdType">执行标志：1.语句；2.存储过程；3.表名；默认执行语句</param>
        /// <param name="sql">执行字符串</param>
        /// <returns>返回DataReader，获取到DataReader后应手动调用关闭方法</returns>
        public SqlDataReader DataReaderle(string sql, int cmdType = 1)
        {
            CommandType isCmdType;
            switch (cmdType)
            {
                case 1:
                    isCmdType = CommandType.Text;
                    break;
                case 2:
                    isCmdType = CommandType.StoredProcedure;
                    break;
                case 3:
                    throw new Exception("不支持：TableDirect只支持OleDB");
                default:
                    throw new Exception("输入参数错误：" + cmdType + "执行标志：1.语句；2.存储过程；3.表名");
            }
            SqlDataReader dr = null;
            try
            {
                Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = isCmdType;
                cmd.CommandText = sql;
                cmd.Connection = conn;
                dr = cmd.ExecuteReader();
            }
            catch (Exception EX)
            {
                Close();
                throw new Exception(EX.Message + "执行SQL：" + sql);
            }
            return dr;
        }
        /// <summary>
        /// 有变量查询数据库返回DataReader
        /// 获取到DataReader后必须手动调用关闭方法Close():
        /// </summary>
        /// <param name="cmdType">执行标志：1.语句；2.存储过程；3.表名；默认执行语句</param>
        /// <param name="sql">执行字符串</param>
        /// <param name="para">参数</param>
        /// <returns>返回DataReader，获取到DataReader后应手动调用关闭方法</returns>
        public SqlDataReader DataReaderle(string sql, SqlParameter[] para, int cmdType = 1)
        {
            CommandType isCmdType;
            switch (cmdType)
            {
                case 1:
                    isCmdType = CommandType.Text;
                    break;
                case 2:
                    isCmdType = CommandType.StoredProcedure;
                    break;
                case 3:
                    throw new Exception("不支持：TableDirect只支持OleDB");
                default:
                    throw new Exception("输入参数错误：" + cmdType + "执行标志：1.语句；2.存储过程；3.表名");
            }
            SqlDataReader dr = null;
            try
            {
                Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = isCmdType;
                cmd.CommandText = sql;
                cmd.Connection = conn;
                cmd.Parameters.AddRange(para);
                dr = cmd.ExecuteReader();
            }
            catch (Exception EX)
            {
                Close();
                StringBuilder sqlArr = new StringBuilder();
                foreach (SqlParameter par in para)
                {
                    sqlArr.Append(par.ParameterName + ":" + par.Value.ToString());
                }
                throw new Exception(EX.Message + "执行出错！:" + sql + ";" + sqlArr.ToString());
            }
            return dr;
        }
        #endregion

        #region 查询DataSet
        /// <summary>
        /// 查询数据库返回DataSet
        /// </summary>
        /// <param name="cmdType">执行标志：1.语句；2.存储过程；3.表名; 默认执行语句</param>
        /// <param name="sql">执行字符串</param>
        /// <returns></returns>
        public DataSet DataSetle(string sql, int cmdType = 1)
        {
            CommandType isCmdType;
            switch (cmdType)
            {
                case 1:
                    isCmdType = CommandType.Text;
                    break;
                case 2:
                    isCmdType = CommandType.StoredProcedure;
                    break;
                case 3:
                    throw new Exception("不支持：TableDirect只支持OleDB");
                default:
                    throw new Exception("输入参数错误：" + cmdType + "执行标志：1.语句；2.存储过程；3.表名");
            }
            DataSet ds = null;
            try
            {
                Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = sql;
                cmd.CommandType = isCmdType;
                cmd.Connection = conn;
                SqlDataAdapter de = new SqlDataAdapter(cmd);
                ds = new DataSet();
                de.Fill(ds);
            }
            catch (Exception EX)
            {
                throw new Exception(EX.Message + "执行出错！:" + sql);
            }
            finally
            {
                Close();
            }
            return ds;
        }
        /// <summary>
        /// 有变量查询数据库返回DataSet
        /// </summary>
        /// <param name="sql">执行字符串</param>
        /// <param name="para">参数</param>
        /// <param name="cmdType">执行标志：1.语句；2.存储过程；3.表名; 默认执行语句</param>
        /// <returns></returns>
        public DataSet DataSetle(string sql, SqlParameter[] para, int cmdType = 1)
        {
            CommandType isCmdType;
            switch (cmdType)
            {
                case 1:
                    isCmdType = CommandType.Text;
                    break;
                case 2:
                    isCmdType = CommandType.StoredProcedure;
                    break;
                case 3:
                    throw new Exception("不支持：TableDirect只支持OleDB");
                default:
                    throw new Exception("输入参数错误：" + cmdType + "执行标志：1.语句；2.存储过程；3.表名");
            }
            DataSet ds = null;
            try
            {
                Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = sql;
                cmd.CommandType = isCmdType;
                cmd.Connection = conn;
                cmd.Parameters.AddRange(para);
                SqlDataAdapter de = new SqlDataAdapter(cmd);
                ds = new DataSet();
                de.Fill(ds);
            }
            catch (Exception EX)
            {
                StringBuilder sqlArr = new StringBuilder();
                foreach (SqlParameter par in para)
                {
                    sqlArr.Append(par.ParameterName + ":" + par.Value.ToString());
                }
                throw new Exception(EX.Message + "执行出错！:" + sql + ";" + sqlArr.ToString());
            }
            finally
            {
                Close();
            }
            return ds;
        }
        #endregion

        #region 其他
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql">/返回数据集SqlDataAdapter(暂未使用)</param>
        /// <returns></returns>
        public SqlDataAdapter Adapter(string sql)
        {
            throw new Exception("暂未使用");
            //conn.Open();
            //SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            //conn.Close();
            //return da;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql">返回da  datagritview更新数据用（暂不使用）</param>
        /// <param name="para"></param>
        /// <returns></returns>
        public SqlDataAdapter Adapter(string sql, SqlParameter[] para)
        {
            throw new Exception("暂未使用");
            //SqlCommand cmd = new SqlCommand(sql, conn);
            //conn.Open();
            //SqlDataAdapter da = new SqlDataAdapter(cmd);
            //cmd.Parameters.AddRange(para);
            //conn.Close();
            //return da;
        }
        

        /// <summary>
        /// 将DataTable更新到对应数据库表
        /// </summary>
        /// <param name="dt">更新的DataTable</param>
        /// <param name="tableName">目标表</param>
        /// <returns></returns>
        public Boolean AddTable(DataTable dt, string tableName)
        {
            try
            {
                Open();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
                {
                    bulkCopy.DestinationTableName = tableName;
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        bulkCopy.ColumnMappings.Add(dt.Columns[i].Caption.ToString(), dt.Columns[i].Caption.ToString());
                    }
                    bulkCopy.WriteToServer(dt);
                    return true;
                }
            }
            catch (Exception EX)
            {
                throw new Exception(EX.Message+"执行出错："+ tableName);
            }
            finally
            {
                Close();
            }
        }
        #endregion

        #region 析构
        /// <summary>
        ///  析构方法
        /// </summary>
        ~SqlServerHelp()
        {
            if (conn != null)
            {
                Close();
                conn.Dispose();
            }
            newTran?.Dispose();
            newTran = null;
            conn = null;
        }
        #endregion
    }
}
